package NicholasDatabase;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;

public class SQLqueries extends SQL {

    public ResultSet voteTopicsQuery(Date date) throws SQLException {
        stat = conn.prepareStatement("select * from votinglist where dateStart >= ? and ? <= dateEnd");
        stat.setDate(1, date);
        stat.setDate(2, date);
        return stat.executeQuery();
    }

    public int insertFeedback(int userId, String remarks, Date dateCreated) throws SQLException {
        stat = conn.prepareStatement("insert into webfeedbacks(userID,remarks,dateCreated) values (?,?,?)");
        stat.setInt(1, userId);
        stat.setString(2, remarks);
        stat.setDate(3, dateCreated);
        return stat.executeUpdate();
    }

    public int insertVote(int voteListId, int userId, String topicVoted, Date dateCreated) throws SQLException {
        stat = conn.prepareStatement("insert into votes values (?,?,?,?)");
        stat.setInt(1, voteListId);
        stat.setInt(2, userId);
        stat.setString(3, topicVoted);
        stat.setDate(4, dateCreated);
        return stat.executeUpdate();
    }

    public int insertVotingList(String topics, Date dateCreated, Date dateStart, Date dateEnd) throws SQLException {
        stat = conn.prepareStatement("insert into votinglist (topics,dateCreated,dateStart,dateEnd) values (?,?,?,?)");
        stat.setString(1, topics);
        stat.setDate(2, dateCreated);
        stat.setDate(3, dateStart);
        stat.setDate(4, dateEnd);
        return stat.executeUpdate();
    }

    public int insertDocument(String pathname, String name, int owner, String category, Date dateCreated) throws SQLException {
        stat = conn.prepareStatement("insert into library (pathname,name,owner,category,dateCreated) values (?,?,?,?,?)");
        stat.setString(1, pathname);
        stat.setString(2, name);
        stat.setInt(3, owner);
        stat.setString(4, category);
        stat.setDate(5, dateCreated);
        return stat.executeUpdate();
    }

    public ResultSet voteQuery(int voteListId, int userId) throws SQLException {
        stat = conn.prepareStatement("select * from votes where votingListId = ? && userId = ?");
        stat.setInt(1, voteListId);
        stat.setInt(2, userId);
        return stat.executeQuery();
    }

    public ResultSet votesByVotingListIdQuery(int votingListId) throws SQLException {
        stat = conn.prepareStatement("select * from votes where votingListId = ?");
        stat.setInt(1, votingListId);
        return stat.executeQuery();
    }

    public ResultSet votingListQuery(Date dateStart) throws SQLException {
        stat = conn.prepareStatement("select * from votinglist where dateEnd >= ?");
        stat.setDate(1, dateStart);
        return stat.executeQuery();
    }

    public ResultSet allVotingListQuery() throws SQLException {
        stat = conn.prepareStatement("select * from votinglist");
        return stat.executeQuery();
    }

    public ResultSet allFeedbacksQuery() throws SQLException {
        stat = conn.prepareStatement("select * from webfeedbacks");
        return stat.executeQuery();
    }

    public ResultSet allDocumentIdsQuery() throws SQLException {
        stat = conn.prepareStatement("select id from library");
        return stat.executeQuery();
    }

    public ResultSet getDocumentByIdQuery(int id) throws SQLException {
        stat = conn.prepareStatement("select * from library where id=?");
        stat.setInt(1, id);
        return stat.executeQuery();
    }

    public ResultSet getDocumentCategoriesQuery() throws SQLException {
        stat = conn.prepareStatement("select category from library");
        return stat.executeQuery();
    }

    public ResultSet getDocumentIdAndTitlesByCategoryQuery(String category) throws SQLException {
        stat = conn.prepareStatement("select id,name from library where category=?");
        stat.setString(1, category);
        return stat.executeQuery();
    }
}
